Ceiling & Floor

There are two other important rounding functions within Excel.  These are the ceiling and floor functions.  Lets take a look at how to use these.

The biggest difference between the ceiling & floor functions and the round up & round down functions is that you can key these functions to any value, while the round functions are keyed to decimal places.

The ceiling function is used to change a particular value to another number above it.  For instance, if you had a group of prices and wanted to change all of the prices to the nearest nickel upward, you would use the ceiling function. 

Skill Builder:  Using the Ceiling Function

1.	Click	Within the Cell where you want the ceiling value to 
		appear.

2.	Type	The function equation.  With this function you will use 
		the following.  
		
		=ceiling(cell address of the value you would like to 
		change, the number to which you would like to 
		increase your value)  
		
		For example, if you wanted to round the value in cell A2  
		to the nearest five cent increment above the current 
		value, you would use the following formula:         
		=ceiling(A2,0.05)

3.  	Press	Enter.  The new ceiling value will appear.

Skill Builder:  Using the Floor Function

If you would rather change a number to a lower increment, you should use the floor function.  This function works much like the ceiling function.

1.	Click	Within the Cell where you want the floor value to 
		appear.

2.	Type	The function equation.  With this function you will use 
		the following.  
		
		=floor(cell address of the value you would like to 
		change, the number to which you would like to 
		decrease your value)  
		
		For example, if you wanted to round the value in cell A2  
		to the nearest five cent increment below the current 
		value, you would use the following formula:         
		=floor(A2,0.05)

3.  	Press	Enter.  The new floor value will appear.